package com.sjsu.CMPE226.normalized.hibernate.app;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.sjsu.CMPE226.normalized.hibernate.util.HibernateUtil;
import com.sjsu.CMPE226.normalized.hibernate.data.Observation;

public class normalizedApp {

	@SuppressWarnings("unchecked")
	public List<Observation> findAllObservationsForDate() {
		List<Observation> obs = null;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			Transaction tx = session.beginTransaction();
			Query query = session
					.createQuery("From Observation o where o.observationsDateTime = '2012-10-06'");
			obs = query.list();
			tx.commit();
		} catch (RuntimeException e) {
			// session.close();
			throw e;
		}

		return obs;
	}

	@SuppressWarnings("unchecked")
	public List<Observation> findAllObservationsForState() {
		List<Observation> obs = null;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			Transaction tx = session.beginTransaction();
			Query query = session
					.createQuery("select s.stationId, s.stationName,"
							+ " o , max(o.temperature), "
							+ "min(o.temperature), max(o.relativeHumidity),"
							+ "min(o.dewPoint), max(o.gust), o.P24I, s.elevation "
							+ "from Observation as o join o.station as s "
							+ "where s.state = 'CA' group by s.stationName ");
			obs = query.list();
			tx.commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return obs;
	}

	@SuppressWarnings("unchecked")
	public List<Observation> findAllObservationsForNetwork() {
		List<Observation> obs = null;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			Transaction tx = session.beginTransaction();
			Query query = session
					.createQuery("Select s.stationId, s.stationName, n.networkName, "
							+ "o, max(o.temperature), "
							+ "min(o.temperature), max(o.relativeHumidity),"
							+ "min(o.dewPoint), max(o.gust), o.P24I, s.elevation "
							+ "from Observation as o "
							+ "join o.station as s "
							+ "join s.network as n "
							+ "where n.networkName='APRSWXNET/CWOP' group by s.stationName ");
			obs = query.list();
			tx.commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return obs;
	}

	@SuppressWarnings("unchecked")
	public List<Observation> findAllObservationsForNetworkAndProvider() {
		List<Observation> obs = null;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			Transaction tx = session.beginTransaction();
			Query query = session
					.createQuery("Select s.stationId, s.stationName, n.networkName, p.providerName,"
							+ "o, max(o.temperature), "
							+ "min(o.temperature), max(o.relativeHumidity),"
							+ "min(o.dewPoint), max(o.gust), o.P24I, s.elevation "
							+ "from Observation as o "
							+ "join o.station as s "
							+ "join s.network as n "
							+ "join s.primaryProvider as p where p.providerName = 'National Weather Service' "
							+ "and n.networkName='NWS/FAA' group by s.stationName ");
			obs = query.list();
			tx.commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return obs;
	}
 
	public int updateStationsForSecondaryStation() {
		int updatedRows = 0;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			Transaction tx = session.beginTransaction();
			Query query = session.createQuery("Update Station s "
					+ "set s.elevation = 15 " + "where s.elevation = 0");
			updatedRows = query.executeUpdate();
			tx.commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return updatedRows;
	}

	public int updateObservationsForSeaLevelPressure() {
		int updatedRows = 0;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			Transaction tx = session.beginTransaction();
			SQLQuery query = session
					.createSQLQuery("update observations o, stations_info s "
							+ "set o.Sea_Level_Pressure = 0 "
							+ "where s.State = 'VA' and "
							+ "o.Sea_Level_Pressure = -9999 and o.Station_Id = s.Station_Id");
			updatedRows = query.executeUpdate();
			tx.commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return updatedRows;
	}

	public int deleteStationsForLatitude() {
		int deletedRows = 0;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			Transaction tx = session.beginTransaction();
			Query query = session
					.createQuery("Delete from Station s where s.latitude > 60");
			deletedRows = query.executeUpdate();
			tx.commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return deletedRows;
	}

	public int deleteObservationsForAltimeter() {
		int deletedRows = 0;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			Transaction tx = session.beginTransaction();
			SQLQuery query = session
					.createSQLQuery("Delete from observations USING observations INNER JOIN stations_info "
							+ "ON observations.Station_Id = stations_info.Station_Id where "
							+ "stations_info.Station_Name like 'CW%' and observations.Altimeter = -9999");
			deletedRows = query.executeUpdate();
			tx.commit();
		} catch (RuntimeException e) {
			session.getTransaction().rollback();
			throw e;
		}

		return deletedRows;
	}
	
	public static void main(String[] args) 
	{
		System.out.println("Test");
		int total = 0;
		normalizedApp app = new normalizedApp();
		
		List<Observation> obs = app.findAllObservationsForDate();
		System.out.println("Find All Observations for Date: " + obs.size());
		
		obs = app.findAllObservationsForState();
		System.out.println("Find All Observations for State : " + obs.size());
		
		obs = app.findAllObservationsForNetwork();
		System.out.println("Find All Observations for Network : " + obs.size());
		
		obs = app.findAllObservationsForNetworkAndProvider();
		System.out.println("Find All Observations for Network & Prov : " + obs.size());
		
		total = app.updateStationsForSecondaryStation();
		System.out.println("Updated Stations for Secondary Station : " + total);
		
		total = app.updateObservationsForSeaLevelPressure();
		System.out.println("Updated Obs for Sea level pressure : " + total);
		
		total = app.deleteStationsForLatitude();
		System.out.println("Deleted Stations for Latitude : " + total);
		
		total = app.deleteObservationsForAltimeter();
		System.out.println("Deleted Obs for Altimeter : " + total);
		
	}

}
